set spark.sql.crossJoin.enabled=true;
with new_net as (
    select distinct network_code    as agent_code,
                    network_name    as agent_name,
                    bs_network_code as bs_agent_code,
                    bs_network_name as bs_agent_name
    from jms_dim.dim_sqs_region_agent_config_base
    where report_type = 3
),
     accept as (
          select create_day
              , accept_proxy_code                                       as proxy_code
              , 0                                                       as work_cnt           -- 呼叫中心
              , 0                                                       as project_cnt        -- 平台
              , sum(if(work_type = '邮政工单', 1, 0))                       as appeal_cnt         -- 邮政
              , 0                                                       as claim_cnt
              , sum(if(work_type = '邮政工单' and duty_type = '第三方', 1, 0)) as third_platform_cnt --三方
              , 0                                                       as transfer_cnt_total
              , sum(if(work_type = '邮政工单' and duty_type = '网点', 1, 0))  as site_cnt
              , 0                                                       as taking_cnt
              , 0                                                       as arrival_cnt
         from jms_dm.dm_waybill_customer_complaint_detail_dt
         where dt = '{{ execution_date | cst_ds }}'
           and is_delete = 2 --2未删除
           and accept_proxy_code <> '888888'
           and accept_proxy_code is not null
         group by create_day, accept_proxy_code -- duty_proxy_code,
     ),
-- 责任网点-代理区
     -- 代理区日理赔客诉量
     duty as (
         select create_day
              , duty_proxy_code                                                             as proxy_code
              , sum(if(work_type = '普通工单', 1, 0))                                           as work_cnt
              , sum(if(work_type = '平台工单', 1, 0))                                           as project_cnt
              , 0                                                                           as appeal_cnt
              , sum(if(work_type = '理赔工单', 1, 0))                                           as claim_cnt
              , sum(if(work_type in ('平台工单', '普通工单', '理赔工单') and duty_type = '第三方', 1, 0))  as third_platform_cnt
              , sum(if(work_type in ('平台工单', '普通工单', '理赔工单') and duty_type = '转运中心', 1, 0)) as transfer_cnt_total
              , sum(if(work_type in ('平台工单', '普通工单', '理赔工单') and duty_type = '网点', 1, 0))   as site_cnt
              , 0                                                                           as taking_cnt
              , 0                                                                           as arrival_cnt
         from jms_dm.dm_waybill_customer_complaint_detail_dt
         where dt = '{{ execution_date | cst_ds }}'
           and is_delete = 2 --2未删除
           and duty_proxy_code <> '888888'
           and duty_proxy_code is not null
         group by create_day, duty_proxy_code
     ),
     proxy_merge as (
         -- 代理区网点收件量-同一运单在同一天只记作一次
         select dt                                                                           as create_day,
--                 dt,
                -- null      as duty_proxy_code,
                if(new_net.bs_agent_code is not null, new_net.agent_code, merges.agent_code) as proxy_code,
                -- max(agent_name) as proxy_name,
                0                                                                            as work_cnt,
                0                                                                            as project_cnt,
                0                                                                            as appeal_cnt,
                0                                                                            as third_platform_cnt,
                0                                                                            as transfer_cnt_total,
                0                                                                            as claim_cnt,
                0                                                                            as site_cnt,
                sum(if(busness_op_type = 1, proxy_bill_cnt, 0))                              as taking_cnt,
                sum(if(busness_op_type = 2, proxy_bill_cnt, 0))                              as arrival_cnt
         from (
                  select net.agent_code,
                         max(net.agent_name) as agent_name,
                         sum(tak_cnt)        as proxy_bill_cnt,
                         1                   as busness_op_type,
                         dt
                  from ( -- 收件量 取 yl_oms_oms_waybill 表 按pick_network_code 分组汇总
                           select pick_network_code, count(1) as tak_cnt, dt
                           from (
                                    select waybill_no
                                         , pick_network_code
                                         , dt
                                         , if(b.del_network_code is not null, 1, 0) as fenmu_tichu_flag
                                    from (
                                             select waybill_no
                                                  , pick_network_code
                                                  , dt
                                                  , substr(input_time, 1, 10) as input_day
                                             from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
                                             where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                                               and dt <= '{{ execution_date | cst_ds }}'
                                               and substr(input_time, 1, 10) >=
                                                   concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                                               and substr(input_time, 1, 10) <= '{{ execution_date | cst_ds }}'
                                             -- and waybill_no like 'JT%' and length(waybill_no) <= 15
                                         ) a
                                             left join
                                         (
                                             select del_network_code, create_day
                                             from jms_dm.dm_waybill_customer_complaint_detail_new_dt
                                             where dt = '{{ execution_date | cst_ds }}'
                                               and del_network_code is not null
                                               and create_day is not null
                                             group by del_network_code, create_day
                                         ) b
                                         on nvl(a.pick_network_code, 'test') = b.del_network_code
                                             and nvl(a.input_day, '9999-12-01') = b.create_day
                                ) tt
                           where fenmu_tichu_flag <> 1
                           group by pick_network_code, dt
                       ) taking
                           left join
                       jms_dim.dim_network_whole_massage net
                       on pick_network_code = net.code
                  where net.network_type = 6
                  group by net.agent_code, dt

                  union
                  -- 到件量 取tab_barscan_sitearrival 表 按单号，扫描网点，扫描时间取最后一条数据，支取JT开头的单号，单号长度<=15的数据
                  select net.agent_code,
                         max(net.agent_name) as agent_name,
                         count(waybill_no)   as proxy_bill_cnt,
                         2                   as busness_op_type,
                         dt
                  from (
                           select scan_site_code,
                                  waybill_no,
                                  dt,
                                  if(b.del_network_code is not null, 1, 0) as fenmu_tichu_flag
                           from (
                                    select scan_site_code
                                         , waybill_no
                                         , date(scan_time) as                                                                       scan_day
                                         , date(scan_time) as                                                                       dt
                                         , row_number()
                                            over (partition by waybill_no, scan_site_code, date(scan_time) order by scan_time desc) rnk
                                    from jms_dwd.dwd_tab_barscan_sitearrival_base_dt
                                    where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                                      and dt <= '{{ execution_date | cst_ds }}'
                                      and substr(scan_time, 1, 10) >=
                                          concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                                      and substr(scan_time, 1, 10) <= '{{ execution_date | cst_ds }}'
                                      and waybill_no like 'JT%'
                                      and length(waybill_no) <= 15
                                ) a
                                    left join
                                (
                                    select del_network_code, create_day
                                    from jms_dm.dm_waybill_customer_complaint_detail_new_dt
                                    where dt = '{{ execution_date | cst_ds }}'
                                      and del_network_code is not null
                                      and create_day is not null
                                    group by del_network_code, create_day
                                ) b
                                on nvl(a.scan_day, '9999-12-01') = b.create_day
                                    and nvl(a.scan_site_code, 'test') = b.del_network_code
                           where a.rnk = 1
                       ) arrival
                           left join jms_dim.dim_network_whole_massage net
                                     on scan_site_code = net.code
                  where net.network_type = 6
                    and fenmu_tichu_flag <> 1
                  group by net.agent_code, dt
              ) merges
                  left join new_net on merges.agent_code = new_net.bs_agent_code
         group by if(new_net.bs_agent_code is not null, new_net.agent_code, merges.agent_code), dt
     ),
     merge1 as (
         select create_day,
--                 dt,
                proxy_code,
                -- max(proxy_name)         as proxy_name,
                sum(work_cnt)           as work_cnt,
                sum(project_cnt)        as project_cnt,
                sum(appeal_cnt)         as appeal_cnt,
                sum(third_platform_cnt) as third_platform_cnt,
                sum(transfer_cnt_total) as transfer_cnt_total,
                sum(claim_cnt)          as claim_cnt,
                sum(taking_cnt)         as taking_cnt,
                sum(arrival_cnt)        as arrival_cnt,
                sum(site_cnt)           as site_cnt -- todo: 新增网点聚合
         from (
                  select *
                  from accept
                  union
                  select *
                  from duty
                  union
                  select *
                  from proxy_merge
              ) t
         where proxy_code is not null --添加null取出哦逻辑
         group by create_day, proxy_code --, duty_proxy_code
     ),
-- insert overwrite  table jms_dm.dm_waybill_customer_complaint_agg_dt partition (dt)
     res1 as (
         select ''                                                                              as regional_code
              , ''                                                                              as regional_desc
              , proxy_code
              , net1.name                                                                       as proxy_name
              , nvl(work_cnt, 0)                                                                as work_cnt      -- 呼叫中心客诉量
              , sum(nvl(work_cnt, 0)) over (partition by create_day)                            as work_total    -- 呼叫中心客诉量汇总
              , nvl(project_cnt, 0)                                                             as project_cnt   -- 平台客诉量
              , sum(nvl(project_cnt, 0)) over (partition by create_day)                         as project_total -- 平台客诉量汇总
              , nvl(appeal_cnt, 0)                                                              as appeal_cnt    -- 邮政客诉量
              , sum(nvl(appeal_cnt, 0)) over (partition by create_day)                          as appeal_total  -- 邮政客诉量汇总
              , nvl(claim_cnt, 0)                                                               as claim_cnt     -- 理赔客诉量
              , sum(nvl(claim_cnt, 0)) over (partition by create_day)                           as claim_total   -- 理赔客户量汇总
-- 第三方客诉量
-- 第三方客诉率
-- 第三方客诉量汇总
-- 第三方汇总客诉率
              , nvl(third_platform_cnt, 0)                                                      as third_platform_cnt
              , round(if(taking_cnt > 0 or arrival_cnt > 0
                          , nvl(third_platform_cnt, 0)
                                / ((nvl(taking_cnt, 0) * 0.15 + nvl(arrival_cnt, 0) * 0.85)) * 10000, 0), 2)
                                                                                                as third_platform_ratio
              , sum(nvl(third_platform_cnt, 0)) over (partition by create_day)                  as third_platform_total
              , round(if(taking_cnt > 0 or arrival_cnt > 0
                          , sum(nvl(third_platform_cnt, 0)) over (partition by create_day)
                                / (((sum(nvl(taking_cnt, 0)) over (partition by create_day)) * 0.15 +
                                    (sum(nvl(arrival_cnt, 0)) over (partition by create_day)) * 0.85)) * 10000, 0), 2)
                                                                                                as third_platform_total_ratio
-- 转运客诉量
-- 转运客诉率 -- 保留2位小数   转运客诉量/【（代理区收件量*15%）+（代理区到件量*85%）】*10000，保留2为小数点
              , nvl(transfer_cnt_total, 0)                                                      as transfer_cnt
              , round(if(taking_cnt > 0 or arrival_cnt > 0,
                         nvl(transfer_cnt_total, 0)
                             / ((nvl(taking_cnt, 0) * 0.15 + nvl(arrival_cnt, 0) * 0.85)) * 10000, 0), 2)
                                                                                                as transfer_cnt_ratio
              , sum(nvl(transfer_cnt_total, 0)) over (partition by create_day)                  as transfer_total
              , round(if(taking_cnt > 0 or arrival_cnt > 0,
                         sum(nvl(transfer_cnt_total, 0)) over (partition by create_day)
                             / (((sum(nvl(taking_cnt, 0)) over (partition by create_day)) * 0.15 +
                                 (sum(nvl(arrival_cnt, 0)) over (partition by create_day)) * 0.85)) * 10000, 0), 2)
                                                                                                as transfer_total_ratio
-- 网点客诉量
-- 网点累计客诉量 所有代理区网点客诉量求和
-- 网点客诉率 保留2位小数 客诉率=客诉量/{(出件量*15%)+(到件量（V1.3更改）*85%)}*10000，保留2位小数点
-- 网点累计客诉率 计算公式：全代理区网点日客诉量/【全代理区（收件量*15%）+（到件量*85%）】*10000，保留2为小数点
              , nvl(site_cnt, 0)                                                                as network_cnt
              , sum(nvl(site_cnt, 0)) over (partition by create_day)                            as network_total
              , round(if(taking_cnt > 0 or arrival_cnt > 0,
                         nvl(site_cnt, 0)
                             / ((nvl(taking_cnt, 0) * 0.15 + nvl(arrival_cnt, 0) * 0.85)) * 10000, 0), 2)
                                                                                                as network_ratio
              , round(if(taking_cnt > 0 or arrival_cnt > 0
                          , sum(nvl(site_cnt, 0)) over (partition by create_day)
                                / (((sum(nvl(taking_cnt, 0)) over (partition by create_day)) * 0.15 +
                                    (sum(nvl(arrival_cnt, 0)) over (partition by create_day)) * 0.85)) * 10000, 0), 2)
                                                                                                as network_total_ratio

--  客诉总量=网点客诉量+第三方客诉量+转运客诉量
-- 全网客诉量
-- 全网客诉量汇总 所有代理区全网客诉量求和
-- 全网客诉率 计算公式：全网客诉量/{(出件量*15%)+(到件量（V1.3更改）*85%)}*10000，保留2位小数点  todo: 调整为4种工单的数量和
              -- , nvl(site_cnt, 0) + nvl(third_platform_cnt, 0) + nvl(transfer_cnt_total, 0) as all_cnt
              , nvl(work_cnt, 0) + nvl(project_cnt, 0) + nvl(appeal_cnt, 0) + nvl(claim_cnt, 0) as all_cnt
              , round(if(taking_cnt > 0 or arrival_cnt > 0,
                         (nvl(work_cnt, 0) + nvl(project_cnt, 0) + nvl(appeal_cnt, 0) + nvl(claim_cnt, 0)) /
                         ((nvl(taking_cnt, 0) * 0.15 + nvl(arrival_cnt, 0) * 0.85)) * 10000, 0), 2)
                                                                                                as all_ratio
              , sum(nvl(site_cnt, 0)) over (partition by create_day)
             + sum(nvl(third_platform_cnt, 0)) over (partition by create_day)
             + sum(nvl(transfer_cnt_total, 0)) over (partition by create_day)                   as all_total
              , round(if(taking_cnt > 0 or arrival_cnt > 0,
                         (sum(nvl(site_cnt, 0)) over (partition by create_day) +
                          sum(nvl(third_platform_cnt, 0)) over (partition by create_day)
                             + sum(nvl(transfer_cnt_total, 0)) over (partition by create_day))
                             / (((sum(nvl(taking_cnt, 0)) over (partition by create_day)) * 0.15 +
                                 (sum(nvl(arrival_cnt, 0)) over (partition by create_day)) * 0.85)) * 10000, 0), 2)
                                                                                                as all_total_ratio
-- 代理区出件量
-- 代理区到件量
              , nvl(taking_cnt, 0)                                                              as taking_cnt
              , nvl(arrival_cnt, 0)                                                             as arrival_cnt
              , create_day                                                                      as date_time
              , create_day                                                                      as dt
         from merge1
                  left join
              (--逻辑调整,去code,name,做网点类型限定
                  select code
                       , name
                  from jms_dim.dim_network_whole_massage
                  where network_type = 2
              ) net1
              on merge1.proxy_code = net1.code
     ),
     proxy_m as (
         select '{{ execution_date | cst_ds }}' as dt
              , ''                              as regional_code
              , ''                              as regional_desc
              , proxy_code                                                  -- 代理区编码
              , max(proxy_name)                 as proxy_name               -- 代理区名称
              , sum(all_cnt)                    as proxy_cnt_month          --当月代理区工单量
              , sum(third_platform_cnt)         as third_platform_cnt_month -- 第三方网点代理区客诉量
              , sum(network_cnt)                as network_cnt_month        -- 网点代理区客诉量
              , sum(transfer_cnt)               as transfer_cnt_month       -- 代理区专业客诉量
              , sum(taking_cnt)                 as proxy_taking_cnt_month   -- 当月代理区收件量
              , sum(arrival_cnt)                as proxy_arrival_cnt_month  -- 当月代理区到件量
              , sum(work_cnt)                   as proxy_work_cnt_month     -- 当月代理区的普通工单客诉量
              , sum(project_cnt)                as proxy_project_cnt_month  -- 当月代理区的普通工单客诉量
              , sum(claim_cnt)                  as proxy_claim_cnt_month    -- 当月代理区的理赔工单客诉量
              , sum(appeal_cnt)                 as proxy_appleal_cnt_month  -- 当月代理区的邮政工单客诉量
         from res1
--          where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
--            and dt <= '{{ execution_date | cst_ds }}'
         group by proxy_code
     )
insert
overwrite
table
jms_dm.dm_waybill_customer_complaint_agg_mt
partition
(
dt
)
select regional_code
     , regional_desc
     , proxy_name
     , proxy_code
-- 代理区月累计
     , proxy_cnt_month                                                     --月累计客诉量-代理区
     , sum(proxy_cnt_month) over ()          as proxy_total_month          -- 月累计客诉量-全网
     , round(nvl(proxy_cnt_month, 0) /
             ((nvl(proxy_taking_cnt_month, 0) * 0.15 + nvl(proxy_arrival_cnt_month, 0) * 0.85)) * 10000, 2)
                                             as proxy_cnt_month_ratio      -- 月累计客诉率-代理区
     , round(sum(nvl(proxy_cnt_month, 0)) over () /
             (((sum(nvl(proxy_taking_cnt_month, 0)) over ()) * 0.15 +
               (sum(nvl(proxy_arrival_cnt_month, 0)) over ()) * 0.85)) * 10000, 2)
                                             as proxy_total_month_ratio    -- 月累计客诉率-全网

     , network_cnt_month                                                   -- 月累计网点客诉量-代理区
     , sum(network_cnt_month) over ()        as network_total_month        -- 月累计网点客诉量-全网
     , round(nvl(network_cnt_month, 0) /
             ((nvl(proxy_taking_cnt_month, 0) * 0.15 + nvl(proxy_arrival_cnt_month, 0) * 0.85)) * 10000, 2)
                                             as network_cnt_month_ratio    -- 月累计网点客诉率-代理区
     , round(sum(nvl(network_cnt_month, 0)) over () /
             (((sum(nvl(proxy_taking_cnt_month, 0)) over ()) * 0.15 +
               (sum(nvl(proxy_arrival_cnt_month, 0)) over ()) * 0.85)) * 10000, 2)
                                             as network_total_month_ratio  -- 月累计网点客诉率-全网


     , third_platform_cnt_month                                            -- 月累计三方客诉量-代理区
     , sum(third_platform_cnt_month) over () as third_platform_total_month -- 月累计三方客诉量-代理区
     , round(nvl(third_platform_cnt_month, 0) /
             ((nvl(proxy_taking_cnt_month, 0) * 0.15 + nvl(proxy_arrival_cnt_month, 0) * 0.85)) * 10000, 2)
                                             as third_cnt_month_ratio      -- 月累计三方客诉率-代理区
     , round(sum(nvl(third_platform_cnt_month, 0)) over () /
             (((sum(nvl(proxy_taking_cnt_month, 0)) over ()) * 0.15 +
               (sum(nvl(proxy_arrival_cnt_month, 0)) over ()) * 0.85)) * 10000, 2)
                                             as third_total_month_ratio    -- 月累计三方客诉率-全网

     , transfer_cnt_month                                                  -- 月累计转单客诉率-代理区
     , sum(transfer_cnt_month) over ()       as transfer_total_month       -- 月累计转单客诉率-全网
     , round(nvl(transfer_cnt_month, 0) /
             ((nvl(proxy_taking_cnt_month, 0) * 0.15 + nvl(proxy_arrival_cnt_month, 0) * 0.85)) * 10000, 2)
                                             as transfer_cnt_month_ratio   -- 月累计转单客诉率-代理区
     , round(sum(nvl(transfer_cnt_month, 0)) over () /
             (((sum(nvl(proxy_taking_cnt_month, 0)) over ()) * 0.15 +
               (sum(nvl(proxy_arrival_cnt_month, 0)) over ()) * 0.85)) * 10000, 2)
                                             as transfer_total_month_ratio -- 月累计转单客诉率-全网

     -- 月累计收件量-代理区
     -- 月累计到件量-代理区
     , proxy_taking_cnt_month
     , proxy_arrival_cnt_month

--渠道汇总
     , nvl(proxy_work_cnt_month, 0)          as proxy_work_cnt_month       -- 当月代理区的普通工单客诉量
     , nvl(proxy_project_cnt_month, 0)       as proxy_project_cnt_month    -- 当月代理区的普通工单客诉量
     , nvl(proxy_claim_cnt_month, 0)         as proxy_claim_cnt_month      -- 当月代理区的理赔工单客诉量
     , nvl(proxy_appleal_cnt_month, 0)       as proxy_appeal_cnt_month     -- 当月代理区的邮政工单客诉量

-- 普通工单客诉量-月累计
-- 普通工单客诉量-月累计
-- 理赔工单客诉量-月累计
-- 邮政工单客诉量-月累计
     , sum(proxy_work_cnt_month) over ()     as work_total_month
     , sum(proxy_project_cnt_month) over ()  as project_total_month
     , sum(proxy_claim_cnt_month) over ()    as claim_total_month
     , sum(proxy_appleal_cnt_month) over ()  as appeal_total_month

     -- 月累计收件量-全网
     -- 月累计到件量-全网
     , sum(proxy_taking_cnt_month) over ()   as proxy_taking_total_month
     , sum(proxy_arrival_cnt_month) over ()  as proxy_arrival_total_month
     , '{{ execution_date | cst_ds }}'       as data_time
     , '{{ execution_date | cst_ds }}'       as dt
from proxy_m
    distribute by 1
;
